package com.example.mybatisplus.demo.easypoi;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.example.mybatisplus.demo.easypoi.dto.*;
import com.example.mybatisplus.demo.easypoi.enums.SexEnum;
import com.example.mybatisplus.demo.easypoi.util.ExcelEasyPoiUtils;
import com.example.mybatisplus.demo.easypoi.util.ExcelExportUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author zengqingfa
 * @className EasyPoiController
 * @description
 * @create 2022/12/18 20:45
 */
@RestController
@RequestMapping("/easyPoi")
@Slf4j
public class EasyPoiController {


    /**
     * 下载Excel
     *
     * @param response
     */
    @PostMapping(value = "/downloadExcel")
    public void downloadExcel(HttpServletResponse response) throws Exception {
        // 构建写入到excel文件的数据
        List<StudentEntity> userExcels = new ArrayList<>();
        StudentEntity userExce1 = new StudentEntity(1001, "张三", 1, new Date());
        StudentEntity userExce2 = new StudentEntity(1002, "李四", 1, new Date());
        StudentEntity userExce3 = new StudentEntity(1003, "王五", 1, new Date());
        StudentEntity userExce4 = new StudentEntity(1004, "赵六", 1, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        ExcelEasyPoiUtils.exportExcel(userExcels, null, "学生列表", StudentEntity.class, "学生列表", response);
    }

    @PostMapping("/uploadExcel")
    public String uploadExcel(@RequestParam("file") MultipartFile file) throws Exception {
        // 读取Excel
        //titleRows:表示标题行 headerRows：表示表头
        List<ImportStudentEntity> studentEntities = ExcelEasyPoiUtils.importExcel(file, 0, 1, ImportStudentEntity.class);
        //获取到导入的数据
        studentEntities.forEach(studentEntity -> System.out.println(studentEntity));
        return "上传Excel文件成功";
    }


    /**
     * 基于模板导出
     *
     * @param response
     * @throws Exception
     */
    @PostMapping(value = "/templateList")
    public void templateList(HttpServletResponse response) throws Exception {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
        // 根据哪个模板进行填充
        TemplateExportParams params = new TemplateExportParams("template/templateList.xlsx");
        Map<String, Object> map = new HashMap<String, Object>();
        List<Member> dataList = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            Member member = new Member();
            member.setCardNo(String.valueOf(123456789012345678L + i));
            member.setName("小明" + (i % 5 < 3 ? 1 : 2));
            member.setCompanyName("神农庄" + (i % 9 < 5 ? 1 : 2));
            member.setBirthday(new Date());
            member.setEmail("zhangsan" + (i) + "@163.com.cn");
            member.setPhoneNumber(String.valueOf(12345678901L + i));
            member.setGender(String.valueOf(i % 2));
            member.setSex(SexEnum.getByCode(i % 2));
            member.setSalary(new BigDecimal(String.valueOf(i + 20000)).divide(new BigDecimal(3), 6, RoundingMode.HALF_UP));
            dataList.add(member);
        }
        map.put("memberList", dataList);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        workbook.write(response.getOutputStream());
    }

    /**
     * 基于模板列表和普通导出
     *
     * @param response
     * @throws Exception
     */
    @PostMapping(value = "/templateList2")
    public void templateList2(HttpServletResponse response) throws Exception {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("验收表", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
        // 根据哪个模板进行填充
        TemplateExportParams params = new TemplateExportParams("template/material_acceptance_template.xlsx");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("projectName", "xxxxxxxx");
        map.put("receiveStorage", "普通仓库1");
        map.put("orderCode", "M2022031600001");
        map.put("orderDate", DateUtil.today());
        map.put("invoiceTitle", "xxx公司");
        map.put("receiveDate", DateUtil.formatChineseDate(new Date(), false));
        map.put("receiveTime", DateUtil.format(new Date(), DatePattern.NORM_TIME_PATTERN));
        map.put("supplierTel", "1879740****");
        map.put("desc", "非常好");
        map.put("carNo", "粤xxxx");
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        for (int i = 0; i < 7; i++) {
            Map<String, String> lm = new HashMap<String, String>();
            lm.put("seq", i + 1 + "");
            lm.put("materialName", "材料" + i);
            lm.put("unit", "个");
            lm.put("price", i * 10000 + "");
            lm.put("actNumber", i * 555 + "");
            lm.put("materialNumber", i * 555 + "");
            lm.put("actNumberCn", i + "大写");
            lm.put("level", "好");
            lm.put("remark", "备注" + i);
            listMap.add(lm);
        }
        map.put("materialList", listMap);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        workbook.write(response.getOutputStream());
    }

    /**
     * 行头增加序列号
     *
     * @param response response
     */
    @PostMapping("/addSerialNo")
    public void addSerialNo(HttpServletResponse response) throws IOException {
        // 构建写入到excel文件的数据
        List<AddIndexStudentEntity> userExcels = new ArrayList<>();
        AddIndexStudentEntity userExce1 = new AddIndexStudentEntity(1001, "张三", 1, new Date());
        AddIndexStudentEntity userExce2 = new AddIndexStudentEntity(1002, "李四", 1, new Date());
        AddIndexStudentEntity userExce3 = new AddIndexStudentEntity(1003, "王五", 1, new Date());
        AddIndexStudentEntity userExce4 = new AddIndexStudentEntity(1004, "赵六", 1, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        ExcelEasyPoiUtils.exportExcel(userExcels, null, "学生列表", AddIndexStudentEntity.class, "学生列表", response);
    }

    /**
     * 行头增加序列号
     *
     * @param response response
     */
    @PostMapping("/addSerialNo2")
    public void addSerialNo2(HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
        // 构建写入到excel文件的数据
        List<AddIndexStudentEntity2> userExcels = new ArrayList<>();
        AddIndexStudentEntity2 userExce1 = new AddIndexStudentEntity2(1001, "张三", 1, new Date());
        AddIndexStudentEntity2 userExce2 = new AddIndexStudentEntity2(1002, "李四", 1, new Date());
        AddIndexStudentEntity2 userExce3 = new AddIndexStudentEntity2(1003, "王五", 1, new Date());
        AddIndexStudentEntity2 userExce4 = new AddIndexStudentEntity2(1004, "赵六", 1, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);

        ExportParams params = new ExportParams();
        params.setSheetName("学生列表");
        params.setType(ExcelType.XSSF);
        //设置addIndex  是否在首列插入序号列， true-是 false-否
        params.setAddIndex(true);
        //com.example.mybatisplus.demo.easypoi.util.ExcelExportUtil
        Workbook workbook = ExcelExportUtil.exportExcel(params, AddIndexStudentEntity2.class, userExcels);
        workbook.write(response.getOutputStream());
    }

    /**
     * 按规则合并单元格
     *
     * @param response response
     */
    @PostMapping("/mergeCell")
    public void mergeCell(HttpServletResponse response) throws IOException {
        // 构建写入到excel文件的数据
        List<MergeCellStudentEntity> userExcels = new ArrayList<>();
        MergeCellStudentEntity userExce1 = new MergeCellStudentEntity(1, "张三", 1, new Date());
        MergeCellStudentEntity userExce2 = new MergeCellStudentEntity(1, "张三", 1, new Date());
        MergeCellStudentEntity userExce3 = new MergeCellStudentEntity(3, "王五", 2, new Date());
        MergeCellStudentEntity userExce4 = new MergeCellStudentEntity(4, "赵六", 2, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        ExcelEasyPoiUtils.exportExcel(userExcels, null, "学生列表", MergeCellStudentEntity.class, "学生列表", response);
    }


    /**
     * 枚举值转换
     *
     * @param response response
     */
    @PostMapping("/enumConvert")
    public void enumConvert(HttpServletResponse response) throws IOException {
        // 构建写入到excel文件的数据
        List<EnumSexStudentEntity> userExcels = new ArrayList<>();
        EnumSexStudentEntity userExce1 = new EnumSexStudentEntity(1, "张三", 1, new Date());
        EnumSexStudentEntity userExce2 = new EnumSexStudentEntity(2, "李四", 1, new Date());
        EnumSexStudentEntity userExce3 = new EnumSexStudentEntity(3, "王五", 2, new Date());
        EnumSexStudentEntity userExce4 = new EnumSexStudentEntity(4, "赵六", 2, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        // 写入数据到excel
        ExcelEasyPoiUtils.exportExcel(userExcels, null, "学生列表", EnumSexStudentEntity.class, "学生列表", response);
    }


    /**
     * 动态列导出
     *
     * @param response response
     */
    @PostMapping("/dynamicColumn")
    public void dynamicColumn(HttpServletResponse response) throws Exception {
        // 构建写入到excel文件的数据
        List<DynamicColumnEntity> userExcels = new ArrayList<>();
        DynamicColumnEntity userExce1 = new DynamicColumnEntity(1, "张三", 1, new Date());
        DynamicColumnEntity userExce2 = new DynamicColumnEntity(2, "李四", 1, new Date());
        DynamicColumnEntity userExce3 = new DynamicColumnEntity(3, "王五", 2, new Date());
        DynamicColumnEntity userExce4 = new DynamicColumnEntity(4, "赵六", 2, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        //只需要导出id和姓名
        this.hihdColumn("sex", true, DynamicColumnEntity.class);
        this.hihdColumn("birthday", true, DynamicColumnEntity.class);

        // 写入数据到excel
        ExcelEasyPoiUtils.exportExcel(userExcels, null, "学生列表", DynamicColumnEntity.class, "学生列表", response);
    }


    /**
     * 动态更改EasyPoi中控制列显示的值
     *
     * @param columnName 需要转换的列属性名称
     * @param target     默认true
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public void hihdColumn(String columnName, Boolean target, Class clazz) throws Exception {
        if (clazz == null) {
            throw new ClassNotFoundException("TARGET OBJECT NOT FOUNT");
        }
        if (StringUtils.isEmpty(columnName)) {
            throw new NullPointerException("COLUMN NAME NOT NULL");
        }
        if (target == null) {
            target = true;
        }
        //获取目标对象的属性值
        Field field = clazz.getDeclaredField(columnName);
        //获取注解反射对象
        Excel excelAnnon = field.getAnnotation(Excel.class);
        //获取代理
        InvocationHandler invocationHandler = Proxy.getInvocationHandler(excelAnnon);
        Field excelField = invocationHandler.getClass().getDeclaredField("memberValues");
        excelField.setAccessible(true);
        Map memberValues = (Map) excelField.get(invocationHandler);
        memberValues.put("isColumnHidden", target);
    }


    /**
     * 生成excel下拉框选项
     *
     * @param response response
     */
    @PostMapping("/generateDropDownBox")
    public void generateDropDownBox(HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                filename + ".xlsx");
        // 构建写入到excel文件的数据
        // 生成地址1W条选项
        List<String> dataList = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            dataList.add("xx省xx市区_" + i);
        }
        // 生成性别选择框
        List<String> sexList = new ArrayList<>();
        sexList.add("男");
        sexList.add("女");
        ExportParams params = new ExportParams();
        params.setSheetName("学生列表");
        params.setType(ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(params, GenerateDropDownBoxEntity.class, Lists.newArrayList());
        workbook.write(response.getOutputStream());
    }
}